package com.dzzw.Dao;

import com.dzzw.Model.LMailBox;
import com.dzzw.utils.DButils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

//领导信箱
public class LMailBoxDao {
    private final Connection con = DButils.getCon();
    //信件新增
    public void LMailBoxAdd(LMailBox lMailBox){
        Date time= new java.sql.Date(new java.util.Date().getTime());
        String sql = "insert into t_leader_mailbox(employee_id,leader_id,leader_title,leader_content,leader_sendtime) value (?,?,?,?,?)";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, lMailBox.getEmployee_id());
            preparedStatement.setInt(2, lMailBox.getLeader_id());
            preparedStatement.setString(3, lMailBox.getLeader_title());
            preparedStatement.setString(4, lMailBox.getLeader_content());
            preparedStatement.setDate(5, (java.sql.Date)time);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据添加失败！");

            e.printStackTrace();
        }
    }

    //信件修改
    public void LMailBoxModify(LMailBox lMailBox){
        String sql = "update t_leader_mailbox set leader_id=?,leader_title=?,leader_content=? where lmail_id=?";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, lMailBox.getLeader_id());
            preparedStatement.setString(2, lMailBox.getLeader_title());
            preparedStatement.setString(3, lMailBox.getLeader_content());
            preparedStatement.setInt(4, lMailBox.getLmail_id());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据修改失败！");

            e.printStackTrace();
        }
    }

    //信件删除
    public void LMailBoxDelete(int lmail_id) {
        String sql = "Delete from t_leader_mailbox where lmail_id = ?";
        try {
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,lmail_id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        }catch(Exception e){
            System.out.println("数据单条删除失败！");

            e.printStackTrace();
        }
    }

    //根据公务员id查询信件
    public static List<LMailBox> SelectLMailBoxByEmployeeId(int employee_id) {

        List<LMailBox> list = new ArrayList<>();
        String sql = "SELECT lm.lmail_id,e.employee_name,l.leader_name,lm.leader_title,lm.leader_content,lm.leader_sendtime FROM t_leader_mailbox AS lm INNER JOIN t_leader AS l ON lm.leader_id = l.leader_id INNER JOIN t_employee AS e ON e.leader_id = l.leader_id AND lm.employee_id = e.employee_id where lm.employee_id = ?";
        try{
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,employee_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                LMailBox lMailBox = new LMailBox();
                lMailBox.setLmail_id(resultSet.getInt(1));
                lMailBox.setEmployee_name(resultSet.getString(2));
                lMailBox.setLeader_name(resultSet.getString(3));
                lMailBox.setLeader_title(resultSet.getString(4));
                lMailBox.setLeader_content(resultSet.getString(5));
                lMailBox.setLeader_sendtime(resultSet.getDate(6));
                list.add(lMailBox);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch(Exception e){
            System.out.println("查询所有数据失败！");
            e.printStackTrace();
        }
        return list;
    }

    //根据领导id查询信件
    public static List<LMailBox> SelectLMailBoxByLeaderId(int leader_id) {

        List<LMailBox> list = new ArrayList<>();
        String sql = "SELECT lm.lmail_id,e.employee_name,l.leader_name,lm.leader_title,lm.leader_content,lm.leader_sendtime FROM t_leader_mailbox AS lm INNER JOIN t_leader AS l ON lm.leader_id = l.leader_id INNER JOIN t_employee AS e ON e.leader_id = l.leader_id AND lm.employee_id = e.employee_id where lm.leader_id = ?";
        try{
            Connection con = DButils.getCon();
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1,leader_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                LMailBox lMailBox = new LMailBox();
                lMailBox.setLmail_id(resultSet.getInt(1));
                lMailBox.setEmployee_name(resultSet.getString(2));
                lMailBox.setLeader_name(resultSet.getString(3));
                lMailBox.setLeader_title(resultSet.getString(4));
                lMailBox.setLeader_content(resultSet.getString(5));
                lMailBox.setLeader_sendtime(resultSet.getDate(6));
                list.add(lMailBox);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch(Exception e){
            System.out.println("查询所有数据失败！");
            e.printStackTrace();
        }
        return list;
    }
}
